using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class Test_LogDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procTest_Log_add]";
        private string _strSPUpdateName = "dbo.[procTest_Log_update]";
        private string _strSPDeleteName = "dbo.[procTest_Log_delete]";
        private string _strSPGetName = "dbo.[procTest_Log_get]";
        private string _strSPGetName_ByNo = "dbo.[procTest_Log_getno]";//procTest_Log_get_ByNo
        private string _strSPGetAllName = "dbo.[procTest_Log_getall]";
        private string _strSPGetPages = "dbo.[procTest_Log_getpaged]";
        private string _strSPIsExist = "dbo.[procTest_Log_isexist]";
        private string _strTableName = "[Test_Log]";
        private string _strSPGetTransferOutName = "dbo.[procTest_Log_gettransferout]";
        private string _strSPGetNoTempName = "dbo.[procTest_Log_getnotemp]";
        private string _strSPGetByNoName = "dbo.[procTest_Log_getno]";
        private string _strSPGetByUserName = "dbo.[procTest_Log_getuser]";
        private string _strSPGetRandomByNo = "procTest_Log_getnorandom";
        string _strSPDeleteTMPName = "procTest_Log_deletetmp";
        string _strSPGetTestUrsName = "procTest_Log_getusertest";
        string _strSPGetByNoScoreName = "procTest_Log_getnoscore";
        #endregion Local Variable

        #region Method
        public Test_LogInfo Get(
        Int32 No,
        Int32 Line,
        ref string sErr)
        {
            Test_LogInfo objEntr = new Test_LogInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(Test_LogInfo.Field.No.ToString(), No);
            AddParameter(Test_LogInfo.Field.Line.ToString(), Line);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (Test_LogInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }
        public DataTable Get_ByNo(
       Int32 No,
       ref string sErr)
        {
            Test_LogInfo objEntr = new Test_LogInfo();
            connect();
            InitSPCommand(_strSPGetName_ByNo);
            AddParameter(Test_LogInfo.Field.No.ToString(), No);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            //if (list.Rows.Count > 0)
            //    objEntr = (Test_LogInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            Test_LogInfo result = new Test_LogInfo();
            result.No = (dt.Rows[i][Test_LogInfo.Field.No.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][Test_LogInfo.Field.No.ToString()]));
            result.Line = (dt.Rows[i][Test_LogInfo.Field.Line.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][Test_LogInfo.Field.Line.ToString()]));
            result.Test_ID = (dt.Rows[i][Test_LogInfo.Field.Test_ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][Test_LogInfo.Field.Test_ID.ToString()]));
            result.User_ID = (dt.Rows[i][Test_LogInfo.Field.User_ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][Test_LogInfo.Field.User_ID.ToString()]));
            result.Question_ID = (dt.Rows[i][Test_LogInfo.Field.Question_ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][Test_LogInfo.Field.Question_ID.ToString()]));
            result.Score = (dt.Rows[i][Test_LogInfo.Field.Score.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][Test_LogInfo.Field.Score.ToString()]));
            result.Creation_Time = (dt.Rows[i][Test_LogInfo.Field.Creation_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][Test_LogInfo.Field.Creation_Time.ToString()]));
            result.Display_Time = (dt.Rows[i][Test_LogInfo.Field.Display_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][Test_LogInfo.Field.Display_Time.ToString()]));
            result.Comment = (dt.Rows[i][Test_LogInfo.Field.Comment.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][Test_LogInfo.Field.Comment.ToString()]));
            result.Change_Time = (dt.Rows[i][Test_LogInfo.Field.Change_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][Test_LogInfo.Field.Change_Time.ToString()]));
            result.Type = (dt.Rows[i][Test_LogInfo.Field.Type.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][Test_LogInfo.Field.Type.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(Test_LogInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(Test_LogInfo.Field.No.ToString(), objEntr.No);
            AddParameter(Test_LogInfo.Field.Line.ToString(), objEntr.Line);
            AddParameter(Test_LogInfo.Field.Test_ID.ToString(), objEntr.Test_ID);
            AddParameter(Test_LogInfo.Field.User_ID.ToString(), objEntr.User_ID);
            AddParameter(Test_LogInfo.Field.Question_ID.ToString(), objEntr.Question_ID);
            AddParameter(Test_LogInfo.Field.Score.ToString(), objEntr.Score);
            AddParameter(Test_LogInfo.Field.Creation_Time.ToString(), objEntr.Creation_Time);
            AddParameter(Test_LogInfo.Field.Display_Time.ToString(), objEntr.Display_Time);
            AddParameter(Test_LogInfo.Field.Comment.ToString(), objEntr.Comment);
            AddParameter(Test_LogInfo.Field.Change_Time.ToString(), objEntr.Change_Time);
            AddParameter(Test_LogInfo.Field.Type.ToString(), objEntr.Type);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(Test_LogInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(Test_LogInfo.Field.No.ToString(), objEntr.No);
            AddParameter(Test_LogInfo.Field.Line.ToString(), objEntr.Line);
            AddParameter(Test_LogInfo.Field.Test_ID.ToString(), objEntr.Test_ID);
            AddParameter(Test_LogInfo.Field.User_ID.ToString(), objEntr.User_ID);
            AddParameter(Test_LogInfo.Field.Question_ID.ToString(), objEntr.Question_ID);
            AddParameter(Test_LogInfo.Field.Score.ToString(), objEntr.Score);
            AddParameter(Test_LogInfo.Field.Creation_Time.ToString(), objEntr.Creation_Time);
            AddParameter(Test_LogInfo.Field.Display_Time.ToString(), objEntr.Display_Time);
            AddParameter(Test_LogInfo.Field.Comment.ToString(), objEntr.Comment);
            AddParameter(Test_LogInfo.Field.Change_Time.ToString(), objEntr.Change_Time);
            AddParameter(Test_LogInfo.Field.Type.ToString(), objEntr.Type);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int32 No,
        Int32 Line
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(Test_LogInfo.Field.No.ToString(), No);
            AddParameter(Test_LogInfo.Field.Line.ToString(), Line);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(Test_LogInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        Int32 No,
        Int32 Line
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(Test_LogInfo.Field.No.ToString(), No);
            AddParameter(Test_LogInfo.Field.Line.ToString(), Line);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(Test_LogInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public int GetMaxNo()
        {
            string query = "Select MAX(No) from " + _strTableName;
            int result = 0;
            connect();
            try
            {
                object tmp = executeScalar(query);
                if (tmp != null && tmp != DBNull.Value)
                    result = Convert.ToInt32(tmp);
                else
                    result = 0;
            }
            catch { }
            disconnect();
            return result;
        }

        public string Delete_TestNo(int maxNo)
        {
            string result = "";
            string query = "Delete  from " + _strTableName + "where No = " + maxNo;
            connect();
            try
            {
                executeNonQuery(query);
            }
            catch (Exception ex) { result = ex.Message; }
            disconnect();
            return result;
        }

        public int GetNoTemp(long idTest,string type)
        {
            string sErr = "";
            int ret = 0;
            connect();
            InitSPCommand(_strSPGetNoTempName);
            AddParameter("ID", idTest);
            AddParameter("Type", type);      
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return ret;
        }

        public DataTable GetLogByNo(long No, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByNoName);
            AddParameter("No", No);
           
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetLogRandByNo(long No, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetRandomByNo);
            AddParameter("No", No);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetByUser(long idTest, long idUser, int createF, int createT, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByUserName);
            AddParameter("IDTest", idTest);
            AddParameter("IDUser", idUser);
            AddParameter("CreateF", createF);
            AddParameter("CreateT", createT);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public string Delete(long idTest, long idUrs, string type)
        {
            connect();
            InitSPCommand(_strSPDeleteTMPName);
            AddParameter(Test_LogInfo.Field.Test_ID.ToString(), idTest);
            AddParameter(Test_LogInfo.Field.User_ID.ToString(), idUrs);
            AddParameter(Test_LogInfo.Field.Type.ToString(), type);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public DataTable GetAll(long idUser, long idTest, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTestUrsName);
            AddParameter("IDUser", idUser);
            AddParameter("IDTest", idTest);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Get_ByNoScore(int no, double score, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByNoScoreName);
            AddParameter("No", no);
            AddParameter("Score", score);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
